package com.sam.spring.chapter6;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcTemplateTest {
	private static JdbcTemplate jdbcTemplate;

	@BeforeClass
	public static void setUpClass() {
		// 表示使用hsqldb内存数据库，数据库名为“test”
		String url = "jdbc:hsqldb:mem:test";
		String username = "sa";
		String password = "";
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
				username, password);
		dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Test
	public void test() {
		// 1.声明SQL
		String sql = "select * from INFORMATION_SCHEMA.SYSTEM_TABLES";

		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// 2.处理结果集
				String value = rs.getString("TABLE_NAME");
				System.out.println("Column TABLENAME: " + value);
			}
		});

	}

	@Before
	public void setUp() {
		String createTableSql = "create memory table test"
				+ "(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
				+ "name varchar(100))";
		jdbcTemplate.update(createTableSql);
	}

	@After
	public void tearDown() {
		String dropTableSql = "drop table test";
		jdbcTemplate.execute(dropTableSql);
	}

	@Test
	public void testCURD() {
		insert();
		delete();
		update();
		select();
	}

	private void insert() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		Assert.assertEquals(2,
				jdbcTemplate.queryForInt("select count(*) from test"));
	}

	private void delete() {
		jdbcTemplate.update("delete from test where name=?",
				new Object[] { "name2" });
		Assert.assertEquals(1,
				jdbcTemplate.queryForInt("select count(*) from test"));
	}

	private void update() {
		jdbcTemplate.update("update test set name='name3' where name=?",
				new Object[] { "name1" });
		Assert.assertEquals(1, jdbcTemplate
				.queryForInt("select count(*) from test where name='name3'"));
	}

	private void select() {
		jdbcTemplate.query("select * from test", new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				System.out.print("====id:" + rs.getInt("id"));
				System.out.println(",name:" + rs.getString("name"));
			}
		});
	}

	@Test
	public void testPpreparedStatement1() {
		int count = jdbcTemplate.execute(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				return conn.prepareStatement("select count(*) from test");
			}
		}, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, DataAccessException {
				pstmt.execute();
				ResultSet rs = pstmt.getResultSet();
				rs.next();
				return rs.getInt(1);
			}
		});
		Assert.assertEquals(0, count);
	}

	@Test
	public void testPreparedStatement2() {
		String insertSql = "insert into test(name) values (?)";
		int count = jdbcTemplate.update(insertSql,
				new PreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement pstmt)
							throws SQLException {
						pstmt.setObject(1, "name4");
					}
				});
		Assert.assertEquals(1, count);
		String deleteSql = "delete from test where name=?";
		count = jdbcTemplate.update(deleteSql, new Object[] { "name4" });
		Assert.assertEquals(1, count);
	}

	@Test
	public void testResultSet1() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		List result = jdbcTemplate.query(listSql, new RowMapper<Map>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rs.getInt("id"), rs.getString("name"));
				return row;
			}
		});
		Assert.assertEquals(1, result.size());
		jdbcTemplate.update("delete from test where name='name5'");
	}

	@Test
	public void testResultSet2() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		final List result = new ArrayList();
		jdbcTemplate.query(listSql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Map row = new HashMap();
				row.put(rs.getInt("id"), rs.getString("name"));
				result.add(row);
			}
		});
		Assert.assertEquals(1, result.size());
		jdbcTemplate.update("delete from test where name='name5'");
	}

	@Test
	public void testResultSet3() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		List result = jdbcTemplate.query(listSql,
				new ResultSetExtractor<List>() {
					@Override
					public List extractData(ResultSet rs) throws SQLException,
							DataAccessException {
						List result = new ArrayList();
						while (rs.next()) {
							Map row = new HashMap();
							row.put(rs.getInt("id"), rs.getString("name"));
							result.add(row);
						}
						return result;
					}
				});
		Assert.assertEquals(1, result.size());
		jdbcTemplate.update("delete from test where name='name5'");
	}
}
